mysql 存储过程

发表于   |   分类于

前言

存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给定参数（如果该存储过程带有参数）来调用执行它。

基本语法

  1. 开始及结束
  2. 参数传递
  3. 变量定义及赋值
  4. 条件判断
  5. 循环
  6. 游标遍历

存储过程pro_handleUserData定义：

// 开始
BEGIN

    // 声明变量
	DECLARE pid,sum bigint;
	DECLARE pname varchar(100);
	DECLARE o int;
	DECLARE createTime,modifyTime datetime;
	DECLARE count int;
	DECLARE b int default 0;
	
	// 声明一个游标
	DECLARE cur_1 CURSOR FOR select FID,FName,FOrder,FCreateTime,FLastModifyTime from t_category 
	where FTradingEntity=userId and FType=3;
	
	// 声明条件处理操作，当游标遍历到末尾时，设置b=1
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET b = 1; 
	
	// 打开游标
	OPEN cur_1;
	
	// 把当前游标提取的值，赋给以下变量
	FETCH cur_1 INTO pid,pname,o,createTime,modifyTime;
	
	// 当变量b等于1时结束循环, b<>1则继续循环
	while b<>1 do 
	
  	    // 变量count赋值
		set count = (select count(1) from t_tag where FID = pid); 
	    set sum = sum + count;
		
		// 条件判断
		if count <= 0 then
		   insert into t_tag(FID,FName,FType,FOrder,FCreateTime,FLastModifyTime) 
		   values(pid,pname,1,o,createTime,modifyTime);
		end if;
		
		// 嵌套调用存储过程
		if count > 0 then
		   call tagProcedure(pid);
		end if;
		
		// 把当前游标提取的值，继续赋给以下变量
		FETCH cur_1 INTO pid,pname,o,createTime,modifyTime;
		
	end while;

    // 关闭游标 
	close cur_1;
	
	// 返回结果
	return sum; 
END
// 结束

输入参数：userId bigint

输出参数：bigint

ibatis存储过程调用：

<procedure id="handleUserData" parameterClass="java.lang.Long">
    <![CDATA[
	   {call pro_handleUserData(#userId:INTEGER#)}
    ]]>
</procedure>

优点与弊端

  1. 优点

    1.1 减少网络通信次数：将业务逻辑封装在存储过程中，业务逻辑层仅需要一次数据库操作即可；

    1.2 事务一致性方面：保证封装在存储过程中一系列的数据库操作事务一致性；

  2. 弊端

    2.1 可移植性方面：当从一种数据库迁移到另外一种数据库时，存储过程要进行修改;

    2.2 可维护性方面：存储过程一般过于复杂，对于后期的开发维护成本过高；

    2.3 性能压力方面：系统性能的瓶颈往往在于数据库并发限制，如果过多的将业务逻辑封装在存储过程中，会延长事务的处理时间，进一步影响数据库并发量；

    2.4 开发调试方面：不便于开发人员调式；

    2.5 代码复用方面：将具体业务逻辑封装在存储过程中，上层业务很难复用同一个存储过程，因为不同的业务总会存在这样或那样的不同；

    2.6 业务拆分方面：当业务发展过大后，往往需要对数据库的业务表按功能模块进行垂直拆分，转移到不同的业务库。这个时候存储过程中业务表之间关联查询等操作，都需要修改拆分；

使用场景

上述已经分析了存储过程使用的优点和弊端，总之，不到万不得已不要使用存储过程，目前很多公司DBA都禁止在数据库层面，使用自定义函数，存储过程，触发器及外键等数据库特性。往往只是在数据库层做一下简单的，功能单一的数据库操作（例如：CRUD增删改查等）。在业务逻辑层通过调用DAO层提供的单一功能，来完成复杂业务逻辑的处理。

但是在有些场景下，存储过程的使用还有比较有用的，例如：清除某个用户下面的所有业务数据，这种操作往往会涉及到多张业务表，并且要严格保证事务的一致性，防止出现部分业务数据清除成功了，而部分清除失败的情况发生。这种场景下使用存储过程相对来说是比较合适的，当然我们也可以在service层保证数据库的事务（上述的前提：同一个用户的所有业务数据都在同一个数据库，否则一旦跨库，很难在分布式环境下，保证事务的一致性）

那么，哪些场景适合使用存储过程呢？

  1. 涉及到操作同一个业务库的多张业务表，且需要保证事务的一致性；
  2. 存储过程使用不频繁的业务功能，例如：用户数据初始化等功能；